Libris Britannia 4/science library(b).zip/science library(b)/PROGRAMM/DB_CLIPP/0769A.ZIP/SHARE.STC

Text File | 1986-04-13 | 14KB | 340 lines

Item number of file to download or <.C>ancel: 8

Item number of file to download or <.C>ancel: 8

              Sharing dBASE III Data with Other Programs
                            By Chris White


Understanding SDF and DELIMITED Files

Introduction


dBASE III can read and write two different forms of text files that
allow the user to transfer data to and from other programs and
operating environments.  The two file structures are, respectively,
SDF and DELIMITED.  The dBASE III commands that can create and read
these file structures are COPY and APPEND.

Both structures are similar.  Each has fields, records, record
separators, and an end-of-file mark.  Additionally, the DELIMITED file
has a field separator and delimiter.  The following table summarizes
the attributes of each structure type.

                        File structure

Attribute               SDF             DELIMITED

Record length        Fixed        Variable
Field length        Fixed        Variable
Record separator    CR/LF*        CR/LF*
Field separator        None        Comma or blank
Delimiter         None         Double quotation (default)
                    or Programmable
End-of-file        Ctrl-Z        Ctrl-Z

* CR/LF denotes the carriage return and line-feed characters.

dBASE III supports all data types except the memo type in reading and
writing SDF and DELIMITED files.  The date type is written and read in
the form YYYYMMDD, with no leading or trailing blanks.  Fields that
are logical type are written as T or F and are read as T/Y or F/N.
There are no leading or trailing blanks, and case is not a
restriction.  The precise manner in which character and numeric types
are written and read is specific to the file structure and is
discussed in the sections that follow.


SDF

Overview

SDF is an acronym for System Data Format.  It is a term used to
describe a file structure whose contents are all ASCII characters.
Each record is a line of text terminated with carriage
return/line-feed characters (0D 0A hex), in that order.  The
end-of-file mark is the Ctrl-Z character (1A hex).  Sometimes this
type of file is referred to as a standard DOS text file.

dBASE III reads and writes this type of file as having fixed-length
fields and records.  This means that there are leading and trailing
blanks.  Numeric fields are right-justified, decimals are aligned, and
blanks are leading.  Character fields are left-justified and have
trailing blanks.  A typical SDF record might look like the following:


   |--------------Record Length = 23-------------|
   -----------------------------------------------------
   |x|x|x|x| | | |z|z|z|z|z| | | | | | |1|4|.|0|0|CR|LF|
   -----------------------------------------------------
   |---Field 1---|------Field 2------|--Field 3--|-----|
     Length = 7      Length = 10       Length = 6   ^
                                                    |   Record
                                                        Separator


When APPENDing into a database file, dBASE III begins with the first
record and takes blocks of characters from left to right that
correspond to fields in the target database file until the CR/LF
characters are encountered.  The SDF record length can be larger or
smaller than the target database file record length.  If the SDF
record length is smaller, all fields will be APPENDed up to the CR/LF;
the remaining fields in the target database file record will remain
blank.  If the target database file record length is smaller, fields
will be APPENDed until there is no more space left in the target
record.  Since there are no field separators in the SDF structure, the
length of the fields in the SDF file must match the field lengths in
the database file precisely.  Otherwise the APPENDed field values will
be misaligned.

If the SDF file has numeric fields that are signed or have decimal
digits, be sure that your database file structure has an extra digit
to hold the sign and that the decimal point has been accounted for.
For example, if the incoming value has the form -999.99, the
appropriate field definition would have a length of seven and two
decimal places: one for the sign, three for the whole number digits,
one for the decimal place , and two for the decimal digits.


Problem Areas

The target database file has only one record after the APPEND process
is complete.

- The file you are importing does not have a CR/LF as a record
separator.

  The following BASIC program will allow you to take a text file with
fixed-length fields and records and insert a CR/LF as a record
separator to create an SDF file.  To use this program, bring up
BASICA, or GWBASIC, and enter it.  When loading your form of BASIC,
make certain that if the input record length is greater than 128
bytes, you have specified the input record length on the DOS command
line that boots BASIC.  For example, if your SDF file record length is
200, the command line will look something like:

        A>BASICA <program name>/S:200
                                        |
             Input record length        |

  Be sure that you specify the names of the input and output files.
Additionally, you must know the length of the input record and the
number of records the input file contains.  (User inputs are bounded
by <>.)

100 ' Initialize the names of the input and output files.
110 INPUT.FILE$  = < Your text file including extension >
120 OUTPUT.FILE$ = < The SDF file including extension >
130 '
140 ' Initialize attributes of input file.
150 LENGTH.OF.REC%  = < Length of the input record >
160 NUMBER.OF.RECS% = < Number of records in the input file >
170 '
180 ' Open input and output files.
190 OPEN "R",1, INPUT.FILE$, LENGTH.OF.REC%
200 FIELD #1, LENGTH.OF.REC% AS RECORD.IN$
210 OPEN "R",2, OUTPUT.FILE$, LENGTH.OF.REC% + 2
220 FIELD #2,LENGTH.OF.REC% AS RECORD.OUT$, 2 AS CR.LF$
230 LSET CR.LF$ = CHR$(13) + CHR$(10)
240 '
250 ' Read / write loop.
260 I = 1
270 WHILE  LOC(1) < NUMBER.OF.RECS%
280     GET #1,I
290     LSET RECORD.OUT$ = RECORD.IN$
300     PUT #2,I
310     I = I + 1
320 WEND
330 '
340 ' Close files and quit to BASIC.
350 CLOSE 1,2
360 END


DELIMITED

Overview

The DELIMITED file, like the SDF file, is a structure that contains
all ASCII characters.  Records are variable-length and and are
separated by the carriage return/line-feed characters (0D 0A hex), in
that order.  Fields are variable-length, have no leading or trailing
blanks, and are separated by commas.  Character fields are bounded by
delimiters.  The default delimiter is a double quotation mark.  The
end-of-file mark is a Ctrl-Z (1A hex).  A typical record of a
DELIMITED file might look like the following:










    Character       Numeric          Date       Logical
        |              |               |           |
        v              v               v           v
|----------------|-------------|-----------------|--|
___________________________________________________________
|"|S|t|r|i|n|g|"|,|1|2|3|.|4|5|,|1|9|8|5|0|1|0|1|,|T|CR|LF|
-----------------------------------------------------------
 ^             ^               ^                       ^
 |__Delimiter__|               |___ Field              |___ Record
                                    Separator               Separator

One of the greatest misunderstandings that dBASE III users have is the
meaning of the delimiter and how it differs from the field separator.
Many believe that the comma is the delimiter.  It unquestionably is
not.  The comma is the field separator.  The delimiter is one of a
pair of characters which marks the bounds of a character field.  It
serves to identify a group of digits as being character type and
allowing embedded commas and spaces.  In dBASE III, the delimiter must
always bound character fields unless the DELIMITED WITH BLANK clause
is used.  The delimiter is, however, programmable using the DELIMITED
WITH <delimiter character> clause.  The field separator is, by
contrast, unconfigurable and must be present unless the DELIMITED WITH
BLANK clause is used.

The DELIMITED WITH BLANK clause allows the user to COPY or APPEND a
text file that has variable-length records and fields but has no
delimiter, and the field separator is a blank.  This is a powerful but
dangerous option.  If a character field has an embedded blank,
APPENDing into a database file will yield ambiguous results.  Fields
that follow a character field that contains an embedded blank will not
be aligned properly and may contain completely erroneous data.  Be
sure to use this option with due consideration and caution.

The APPEND operation begins with the first field and proceeds left to
right, taking a character at a time from the DELIMITED file and
placing it into the current field of the target database field.  This
continues until the field separator is encountered or until there is
no more space in the current target database field.  This process
proceeds until the record separator or end-of-file mark is sensed.
Like the SDF structure, incoming records can be longer or shorter in
length than database record length.  If the DELIMITED record length is
smaller, all fields will be APPENDed up to the CR/LF; the remaining
fields in the target database record will remain blank.  If the target
database record length is smaller, fields will be APPENDed until there
is no more space left in the target record.  Unlike the SDF structure,
an incoming DELIMITED field length can be longer or shorter than the
database field length.  If the DELIMITED field length is smaller, the
field will be APPENDed up to the record separator.  If the DELIMITED
field length is larger than the database field, the field will be
APPENDed until there is no more space left in the database field.


Problem Areas

(1) The target database file has blank or zero fields where the
concomitant DELIMITED fields have data.

- A character field in the DELIMITED file has unbalanced or no
delimiters.  All fields that follow an incorrectly delimited character
field will have a zero or blank value.


(2) The target database file has offset fields, some of them
containing delimiters.

- The DELIMITED WITH clause of the APPEND command was executed with no
delimiter.


How the FOR Condition Operates

When COPYing to a DELIMITED or SDF file, the FOR condition operates
like any other command that allows a FOR condition.  With the APPEND
command it operates conceptually somewhat differently.  The incoming
data is APPENDed into the target database file.  The new record is
then tested to determine if it meets the specified FOR condition.  If
it does not, it is discarded.  In specifying the proper FOR condition,
you must take care to test the incoming record as if it were a part of
the target database file.




For example, you have a DELIMITED file containing a date in the form
YYYYMMDD, which is the format necessary to APPEND into a field of date
type.  You wish to APPEND only records that have a date field value
that falls before 01/01/85.  The correct syntax is:

    APPEND FROM YourFile DELIMITED FOR Date < CTOD("01/01/85")

Incorrect syntax might be:

   APPEND FROM YourFile DELIMITED FOR Date < "19850101"


dBASE III to WordStar/MailMerge

dBASE III can quite easily interface with WordStar/MailMerge's form
letter and merging facilities.  To send a data file to MailMerge,
create a DELIMITED file from your database file.  Typical syntax is as
follows:

    COPY TO Yourfile DELIMITED

Most WordStar/MailMerge documentation states that a DELIMITED data
file is required.  The examples, however, show a DELIMITED file with
no delimiters.  But this is not a problem.  MailMerge supports the
double quotation mark as a delimiter if the DELIMITED file has one.

To set up a form letter in WordStar/MailMerge, go into WordStar and
enter the text of the letter as you would for any other, with some
minor exceptions.  First, using the dot command .DF, open the
DELIMITED file that dBASE III has created.  Next, using the dot
command .RV, list the variables that reflect all the fields in the
DELIMITED file.  For example:

    .DF Yourfile.txt
    .RV Field1,Field2,...FieldN

If for some reason you need a subset of the fields from your dBASE III
database file for your MailMerge application, use the FIELDS option of
the COPY command.  This is an important consideration.  The variable
list specified after the .RV is not selective.  Variables are appended
into the letter form left to right, much like APPENDing into a dBASE
III database file from a DELIMITED file.

The next major consideration is how to use variables within your
document.  There are only two rules.  First, the variables used within
the document must be bounded by ampersands (&) in order to be
recognized as such.  Second, to suppress the printing of variables
with blank values, add the suffix /O before the terminating ampersand
character.  Last, begin the document with the dot command .OP, to
suppress page numbering and end the document with the dot command .PA,
to start the next print document on a new page.

Example of form letter document:

.OP
.DF dBASEIII.TXT
.RV Last,First,Salutation,Title,Company,Street,City,State,Zip

&First& &Last&
&Title/O&
&Company/O&
&Street&
&City&, &State&  &Zip&

Dear &Salutation&. &Last&

Congratulations, you have just received a form letter.  Your name and
address are held in an indexed database file.  Periodically, you may
receive a form letter.

Thank you for your kind patience.

Sincerely,



The Mad Letterer
.PA

Would you like to download another file (Y/N)?